package com.central.apps.dao.MaterialInventory;

import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface transferstoDao  {
    @Select("select ry.itemnum,ry.itemdesc,ry.model,ry.specifications,es.id,es.binnum,es.batchnum,es.avblbalance,es.unitcost,es.orderunit,es.version,es.subjectno,es.proid from gq_invbalances es\n" +
            "left join gq_inventory ry on ry.storenum = es.storenum and ry.itemnum = es.itemnum\n" +
            "where ry.storenum = #{storenum} and es.subjectno = #{primarysubjectno} AND ( es.applyby IS NULL OR es.applyby = #{targetdemandid} )\n" +
            "and es.batchnum not in (select fromlot from gq_transferstoitem where transferstoid = #{transferstoid}) " +
            "and es.avblbalance > 0")
    List<Map<String, Object>> getInventoryList(String storenum, String primarysubjectno,String transferstoid,String targetdemandid);
    //课题号为空
    @Select("select ry.itemnum,ry.itemdesc,ry.model,ry.specifications,es.id,es.binnum,es.batchnum,es.avblbalance,es.unitcost,es.orderunit,es.version,es.subjectno,es.proid from gq_invbalances es\n" +
            "left join gq_inventory ry on ry.storenum = es.storenum and ry.itemnum = es.itemnum\n" +
            "where ry.storenum = #{storenum}  AND ( es.applyby IS NULL OR es.applyby = #{targetdemandid} )\n" +
            "and es.batchnum not in (select fromlot from gq_transferstoitem where transferstoid = #{transferstoid}) " +
            "and es.avblbalance > 0")
    List<Map<String, Object>> getInventoryList3(String storenum, String transferstoid,String targetdemandid);
    @Select("select ry.itemnum,ry.itemdesc,ry.model,ry.specifications,es.id,es.binnum,es.batchnum,es.avblbalance,es.unitcost,es.orderunit,es.version,es.subjectno,es.proid from gq_invbalances es\n" +
            "            left join gq_inventory ry on ry.storenum = es.storenum and ry.itemnum = es.itemnum\n" +
            "            where ry.storenum = #{storenum} and es.subjectno = #{primarysubjectno} \n" +
            "            and es.batchnum not in (select fromlot from gq_transferstoitem where transferstoid = #{transferstoid})\n" +
            "            and es.avblbalance > 0 AND ( es.applyby IS NULL OR es.applyby = #{targetdemandid} )")
    List<Map<String, Object>> getInventoryList1(String storenum, String primarysubjectno,String transferstoid,String targetdemandid);
    //课题号为空
    @Select("select ry.itemnum,ry.itemdesc,ry.model,ry.specifications,es.id,es.binnum,es.batchnum,es.avblbalance,es.unitcost,es.orderunit,es.version,es.subjectno,es.proid from gq_invbalances es\n" +
            "            left join gq_inventory ry on ry.storenum = es.storenum and ry.itemnum = es.itemnum\n" +
            "            where ry.storenum = #{storenum}  \n" +
            "            and es.batchnum not in (select fromlot from gq_transferstoitem where transferstoid = #{transferstoid})\n" +
            "            and es.avblbalance > 0 AND ( es.applyby IS NULL OR es.applyby = #{targetdemandid} )")
    List<Map<String, Object>> getInventoryList2(String storenum,String transferstoid,String targetdemandid);
    //根据库存转移主表编码查明细行
    @Select("select id from gq_transferstoitem where transferstoid = #{transferstoid}")
    List<String> findTransferstoitemList(String transferstonum);

    //根据id查库存转移
    @Select("select outwarehousenum,inwarehousenum,recipients from gq_transfersto where id = #{id}")
    Map<String, Object> findTransfersto(String id);

    @Select("select curbal from gq_invbalances where storenum = #{outwarehousenum} " +
            "and itemnum = #{itemnum} and binnum = #{frombin} and batchnum = #{fromlot}")
    Integer getCurbal(String frombin, String fromlot, String outwarehousenum, String itemnum);

    @Select("select curbaltotal")
    Map<String, Object> getInventoryMap(String storenum, String itemnum);

    @Update("update gq_invbalances set curbal = curbal - #{quantity},reservedqty =reservedqty - #{quantity},avblbalance = curbal - reservedqty " +
            "where storenum = #{storenum} and itemnum = #{itemnum} and binnum = #{binnum} and batchnum = #{batchnum}")
    //
    void updateInventory(Map<String, Object> params);
    @Insert("INSERT into gq_invbalances(storenum,itemnum,binnum,batchnum,curbal,avblbalance)VALUES(#{map.storenum},#{map.itemnum},#{map.binnum},#{map.batchnum},#{map.quantity},#{map.quantit})")
    int insert(@Param("map") Map<String, Object> map);

//    @Update("update gq_invbalances set curbal = curbal - #{quantity} " +
//            "where storenum = #{storenum} and itemnum = #{itemnum} and binnum = #{binnum} and batchnum = #{batchnum}")
//    void updateInwarehousenum(Map<String, Object> params);



}
